﻿CREATE PROCEDURE [steve].[spSteve_GetContentByAnyCategory]
	@OptionalCategoryNames CategoryNameTableType readonly,
	@Top bigint,
	@ApprovedOnly bit,
	@ExcludeContentId uniqueidentifier,
	@ContentTypeName varchar(50)
AS
	
	select distinct top (@Top) cdata.ContentId
		, cdata.ContentTypeName
		, cdata.CreateDate
		, cdata.CreateMembershipId
		, cdata.ExternalId
		, cdata.SiteUrlId
		, cdata.[Language]
		, cdata.LatestRevisionNumber
		, cdata.LatestApprovedRevisionNumber
		, cdata.RevisionNumber
		, cdata.[Path]
		, cdata.SystemName
		, cdata.Author
		, cdata.ApprovalDate
		, cdata.ApprovalMembershipId
		, cdata.ContentDataId
		, cdata.Height
		, cdata.MimeSubtype
		, cdata.MimeType
		, cdata.PublicationDate
		, cdata.Publisher
		, cdata.PublisherUrlId
		, cdata.RevisionComment
		, cdata.RevisionDate
		, cdata.RevisionMembershipId
		, cdata.Teaser
		, cdata.ThumbnailContentId
		, cdata.Title
		, cdata.Width
		, cdata.SiteName
		, cdata.Url
		, cdata.IsSecure
		, cdata.DefaultAuthority

		, convert(decimal(8,7), null) CategorySimilarity
	from ContentCategory cc
		inner join @OptionalCategoryNames names on names.CategoryName = cc.CategoryName
		inner join uvSteve_ContentRevision cdata on cdata.ContentId = cc.ContentId
	where cc.DeleteDate is null
		and (@ExcludeContentId is null or @ExcludeContentId <> cc.ContentId)
		and ((@ApprovedOnly = 0 and cdata.RevisionNumber = cdata.LatestRevisionNumber)
			or (@ApprovedOnly = 1 and cdata.RevisionNumber = cdata.LatestApprovedRevisionNumber))
		and (@ContentTypeName is null or cdata.ContentTypeName = @ContentTypeName)

RETURN 0